﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Security.Cryptography.X509Certificates;
using System.Security.Cryptography;

/// <summary>
///DB 的摘要说明
/// </summary>
public class DB_MSSQL_Helper
{
    /// <summary>
    /// 获取SQL的连接字符串
    /// </summary>
    /// <returns></returns>
    private static SqlConnection getConn()
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = "data source=.;database=GameTest;integrated security=sspi";
        return conn;
    }
    /// <summary>
    /// 获取自定义的SQL的连接字符串
    /// </summary>
    /// <param name="connect"></param>
    /// <returns></returns>
    private static SqlConnection getnewConn(string connect)
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = connect;
        return conn;
    }
    /// <summary>
    /// 查单值的通用方法
    /// 返回object的一个数据
    /// 无SQL注入 
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static object getSingle(string sql)
    {
        SqlConnection conn =DB_MSSQL_Helper.getConn();
        conn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = sql;
        object result = cmd.ExecuteScalar();
        conn.Close();
        return result;
    }
    /// <summary>
    /// 查单值的通用方法
    /// 返回object的一个数据
    /// 无SQL注入 
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="SqlParameter_list"></param>
    /// <returns></returns>
    public static object getSingle(string sql, List<SqlParameter> SqlParameter_list)
    {
        SqlConnection conn = DB_MSSQL_Helper.getConn();
        conn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = sql;
        for (int i = 0; i < SqlParameter_list.Count; i++)
        {
            cmd.Parameters.Add(SqlParameter_list[i]);
        }
        object result = cmd.ExecuteScalar();
        cmd.Parameters.Clear();
        conn.Close();
        return result;
    }
    /// <summary>
    /// 执行sql查询,返回SqlDataReader对象
    /// 无SQL注入 
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static SqlDataReader getReader(string sql)
    {
        SqlConnection conn = DB_MSSQL_Helper.getConn();
        conn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = sql;
        SqlDataReader rd = cmd.ExecuteReader();
        if (!rd.HasRows)
        {
            conn.Close();
        }
        return rd;
    }
    /// <summary>
    /// 执行sql查询,返回SqlDataReader对象
    /// 无SQL注入 
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="SqlParameter_list"></param>
    /// <returns></returns>
    public static SqlDataReader getReader(string sql, List<SqlParameter> SqlParameter_list)
    {
        SqlConnection conn = DB_MSSQL_Helper.getConn();
        conn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = sql;
        for (int i = 0; i < SqlParameter_list.Count; i++)
        {
            cmd.Parameters.Add(SqlParameter_list[i]);
        }
        SqlDataReader rd = cmd.ExecuteReader();
        if (!rd.HasRows)
        {
            conn.Close();
        }
        cmd.Parameters.Clear();
        return rd;
    }
    public static bool upDate(string sql)
    {
        SqlConnection conn = DB_MSSQL_Helper.getConn();
        conn.Open();
        int ExecuteNonQuery_number = ExecuteNonQuery(sql, conn);
        return ThreeEyeOperatorHelper.GetType(ExecuteNonQuery_number, -1, false, true);
    }
    /// <summary>
    /// 增、删、改的通用方法
    /// 执行Sql语句或存储过程，返回受影响的行数
    /// 无SQL注入 
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="conn"></param>
    /// <returns></returns>
    private static int ExecuteNonQuery(string sql, SqlConnection conn)
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = sql;
        return cmd.ExecuteNonQuery();
    }
    public static bool upDate(string sql,List<SqlParameter> SqlParameter_list)
    {
        SqlConnection conn = DB_MSSQL_Helper.getConn();
        conn.Open();
        int ExecuteNonQuery_number = ExecuteNonQuery(sql, SqlParameter_list, conn);
        return ThreeEyeOperatorHelper.GetType(ExecuteNonQuery_number,-1,false,true);
    }
    /// <summary>
    /// 增、删、改的通用方法
    /// 执行Sql语句或存储过程，返回受影响的行数
    /// SQL注入 
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="SqlParameter_list"></param>
    /// <param name="conn"></param>
    /// <returns></returns>
    private static int ExecuteNonQuery(string sql, List<SqlParameter> SqlParameter_list, SqlConnection conn)
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = sql;
        for (int i = 0; i < SqlParameter_list.Count; i++)
        {
            cmd.Parameters.Add(SqlParameter_list[i]);
        }
        int ExecuteNonQuery_number = cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        return ExecuteNonQuery_number;
    }
    /// <summary>
    /// 执行查询，数据填充到DataSet（多个结果集）
    /// 无SQL注入 
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static DataSet getSet(string sql)
    {
        SqlConnection conn = DB_MSSQL_Helper.getConn();
        SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
        DataSet ds = new DataSet();
        sda.Fill(ds);
        return ds;
    }
    /// <summary>
    /// 执行查询，数据填充到DataSet（多个结果集）
    /// SQL注入 
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="SqlParameter_list"></param>
    /// <returns></returns>
    public static DataSet getSet(string sql, List<SqlParameter> SqlParameter_list)
    {
        SqlConnection conn = DB_MSSQL_Helper.getConn();
        SqlCommand cmd = new SqlCommand(sql,conn);
        for (int i = 0; i < SqlParameter_list.Count; i++)
        {
            cmd.Parameters.Add(SqlParameter_list[i]);
        }
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        sda.Fill(ds);
        cmd.Parameters.Clear();
        return ds;
    }
    /// <summary>
    /// 执行查询，数据填充到DataTable
    /// 无SQL注入 
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static DataTable getDataTable(string sql)
    {
        SqlConnection conn = DB_MSSQL_Helper.getConn();
        SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
        DataTable ds = new DataTable();
        sda.Fill(ds);
        return ds;
    }
    /// <summary>
    /// 执行查询，数据填充到DataTable
    /// SQL注入 
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="SqlParameter_list"></param>
    /// <returns></returns>
    public static DataTable getDataTable(string sql, List<SqlParameter> SqlParameter_list)
    {
        SqlConnection conn = DB_MSSQL_Helper.getConn();
        SqlCommand cmd = new SqlCommand(sql, conn);
        for (int i = 0; i < SqlParameter_list.Count; i++)
        {
            cmd.Parameters.Add(SqlParameter_list[i]);
        }
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        DataTable ds = new DataTable();
        sda.Fill(ds);
        cmd.Parameters.Clear();
        return ds;
    }

}